#!/usr/bin/env python
# coding=utf-8
import akshare as ak
import pandas as pd
import numpy as np
import time
import datetime
from include import dataBase
from include import dealCode
from include import dealDate
from include import dealHistory
from include import dealPool

class dealMarket:
    def __init__(self,connect,dealDate,dealCode,dealHistory,dealPool):
        self.connect = connect
        self.dealDate = dealDate
        self.dealCode = dealCode
        self.dealHistory = dealHistory
        self.dealPool = dealPool

    def getMarketAll(self):
        ''' 通过拆分每只股票的历史数据，得到当天的基础市场行情数据并存储，表名：  '''
        # 获取交易日信息
        date = self.dealDate.getAllDate()
        # 获取最新股票代码
        code =  self.dealCode.getAllCode()
        # 判断有无上述数据
        if date is None or code is None:
            print("dealMarket 获取交易日信息或股票代码错误")
        # 遍历每一天、每只股票组合获取当天的市场基本数据
        for (everyDate,) in date:
            self.getMarketOne(everyDate)
        print("整合股票历史数据完毕")

    def getMarketOne(self,everyDate):
        ''' everyDate:日期类型 '''
        if isinstance(everyDate,str):
            print("getMarketOne 需要的参数是日期类型而不是str类型")
            return -1

        print("整合当天股票信息: {}".format(everyDate))
        # 创建当日基础信息表，命名：date
        sql = '''  CREATE TABLE IF NOT EXISTS `{}` (
            stockCode VARCHAR(10) NOT NULL,
            openPrice DECIMAL(10, 2) NOT NULL,
            closePrice DECIMAL(10, 2) NOT NULL,
            highPrice DECIMAL(10, 2) NOT NULL,
            lowPrice DECIMAL(10, 2) NOT NULL,
            openChange DECIMAL(7, 2) NOT NULL,
            closeChange DECIMAL(7, 2) NOT NULL,
            highChange DECIMAL(7, 2) NOT NULL,
            lowChange DECIMAL(7, 2) NOT NULL,
            volume INT NOT NULL,
            turnover DECIMAL(20, 2) NOT NULL,
            amplitude DECIMAL(7, 2) NOT NULL,
            changePercent DECIMAL(7, 2) NOT NULL,
            turnoverRate DECIMAL(5, 2) NOT NULL,
            maxProfit DECIMAL(10, 2) NOT NULL,
            maxLoss DECIMAL(10, 2) NOT NULL,
            isTop INT NOT NULL,
            PRIMARY KEY (stockCode)
            );  '''.format(everyDate.strftime("%Y-%m-%d") + "test")
        self.connect.create(everyDate.strftime("%Y-%m-%d") + "test",sql)
        # 遍历每一只股票
        code =  self.dealCode.getAllCode()
        for (everyCode,) in code:
            print(everyDate,everyCode)
            # 判断当天是否上市
            if not self.isList(everyDate ,everyCode):
                continue
            # 判断是否更新了最新日期
            if not self.isNewest(everyDate,everyCode):
                # 后续补充更新数据操作
                continue
            # 进行查找
            temp = self.dealHistory.getDateHistory(everyDate,everyCode)
            if temp:
                price = temp[0]
            else:
                continue
                
            yesPrice = price[2]/(1 + price[8]/100)
            sql = ''' insert into `{}` values('{}',{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{})on duplicate key update stockCode = '{}';
                    '''.format(everyDate.strftime("%Y-%m-%d") + "test",everyCode,price[1],price[2],price[3],price[4],round((price[1]/yesPrice - 1)*100,2),round((price[2]/yesPrice - 1)*100,2),
                    round((price[3]/yesPrice - 1)*100,2),round((price[4]/yesPrice - 1)*100,2),price[5],price[6],price[7],price[8],price[10],
                    round((price[2] - price[4])/yesPrice*100,2),round((price[3] - price[2])/yesPrice*100,2),price[11],everyCode)
            self.connect.insert(everyDate.strftime("%Y-%m-%d") + "test",sql)
        self.connect.commit()

    def isList(self,date,code):
        ''' 判断当天股票是否上市  '''
        sql = ''' select min(date) from `{}`;  '''.format(code)
        minDate = self.connect.select(code,sql)
        return minDate is not None and minDate[0][0] is not None and date >= minDate[0][0]

    def isNewest(self,date,code):
        ''' 判断股票库是否更新了最新数据  '''
        sql = ''' select max(date) from `{}`;  '''.format(code)
        maxDate = self.connect.select(code,sql)
        return maxDate is not None and maxDate[0][0] is not None and date <= maxDate[0][0]

    def getLimitData(self,limit,everyDate,dailyData):
        ''' 获取当天的大涨、大跌的数量、平均成交量以及平均市值
            linit:     top or down  str
            everyDate: 日期 str
            dailyData: 当天的数据字典 dict
            '''
        sql = '''  select turnover/(turnoverRate + 0.0001)*100 as marketValue,turnover from `{}Pool{}` as a
                    INNER JOIN  `{}` as b on a.`code` = b.stockCode;'''.format(limit,everyDate,everyDate)
        data = list(zip(*(self.connect.select(limit + "Pool" + everyDate,sql))))
        if len(data) != 0:
            dailyData[ limit + 'Count'] = len(data[0])
            dailyData[ limit + 'AvgMarketValue'] = round(np.mean(data[0]),2)
            dailyData[ limit + 'AvgTurnover'] = round(np.mean(data[1]),2)

    def getFluctuData(self,fluctuate,everyDate,dailyData):
        ''' 获取当天的大肉、大面的数量
            fluctuate: meat or noodle
            其余参数同上
        '''
        sql = '''  select turnover/(turnoverRate + 0.0001)*100 as marketValue,turnover from `{}Pool{}`
                    as a INNER JOIN  `{}` as b on a.`code` = b.stockCode;'''.format(fluctuate,everyDate,everyDate)
        data = list(zip(*(self.connect.select(fluctuate + "Pool" + everyDate,sql))))
        if len(data) != 0:
            dailyData[ fluctuate + 'Count' ]  = len(data[0])

    def getGrowthLimitData(self,limit,everyDate,dailyData):
        ''' 获取当天创业板大涨、大跌数 '''
        sql  = '''  select 1
                    from `{}Pool{}` as a
                    INNER JOIN `code` as b
                    on a.`code` = b.`code`
                    where b.market = '创业板';  '''.format(limit,everyDate)
        data = self.connect.select(limit + "Pool" + everyDate,sql)
        dailyData[ "growth" + limit.capitalize() + "Count"  ] = len(data)

    def getPreminum(self,limit,everyDate,yesDate,dailyData):
        ''' 获取昨日大涨、大跌、大肉、大面今日开盘溢价/折价 '''
        sql = ''' select a.`code`
                         from `{}Pool{}` as a
                         INNER JOIN `code` as b
                         on a.`code` = b.`code` '''.format(limit,yesDate)
        data = list(zip(*(self.connect.select(limit + "Pool" + yesDate.strftime("%Y-%m-%d"),sql))))
        if len(data) != 0:
            dailyData[limit + "Preminum" if limit in ["top","meat"] else limit + "Discount"] = self.dealPool.getOpenPool(everyDate,data[0])

    def getGrowthPreinum(self,limit,everyDate,yesDate,dailyData):
        ''' 创业板大涨溢价、大跌折价  '''
        sql = ''' select a.`code`
                         from `{}Pool{}` as a
                         INNER JOIN `code` as b
                         on a.`code` = b.`code`
                         where b.market="创业板"
                         '''.format(limit,yesDate)
        data = list(zip(*(self.connect.select(limit + "Pool" + yesDate.strftime("%Y-%m-%d"),sql))))
        if len(data) != 0:
            dailyData[limit + "Preminum" if limit in ["top","meat"] else limit + "Discount"] = self.dealPool.getOpenPool(everyDate,data[0])

    def getTopTire(self,everyDate,dailyData):
        ''' 获取涨停元组：[[(...股票名称、涨停天数、含断板天数)...每个梯队的股票]...存储三十个梯队]  '''
        sql = ''' select a.code,name from `topPool{}` as a inner join `code`
                    as b on a.`code` = b.`code`;'''.format(everyDate)
        topPoolTire = list(zip(*(self.connect.select("topPool" + everyDate,sql))))
        if topPoolTire[0] is None:
            return
        topTire = [[()] for _ in range(30)]
        for i in range(len(topTire[0])):
            sql = ''' select isTop,topAllDayNum,topDayNum from `{}` where date='{}' ;
                    '''.format(topPoolTire[0][i],everyDate)
            temp = self.connect.select(topPoolTire[0][i],sql)[0]
            if temp[0] != 1:
                return
            topTire[temp[2]].append((topPoolTire[1][i],temp[1],temp[2]))
        dailyData['topTire'] = str(topTire)

    def dealMarket(self):
        ''' 统计当天市场的涨停、跌停等处理数据并存储，表名：dealMarket  '''
        # 创建表dealMarket
        sql = '''  CREATE TABLE dealMarketTest (
                                    date DATE PRIMARY KEY,
                                    topCount INT DEFAULT 0,
                                    downCount INT DEFAULT 0,
                                    meatCount INT DEFAULT 0,
                                    noodleCount INT DEFAULT 0,
                                    topPreminum DECIMAL(10, 2) DEFAULT 0.00,
                                    downDiscount DECIMAL(10, 2) DEFAULT 0.00,
                                    meatPreminum DECIMAL(10, 2) DEFAULT 0.00,
                                    noodleDiscount DECIMAL(10, 2) DEFAULT 0.00,
                                    growthTopCount INT DEFAULT 0,
                                    growthDownCount INT DEFAULT 0,
                                    growthTopPreminum DECIMAL(10, 2) DEFAULT 0.00,
                                    growthDownDiscount DECIMAL(10, 2) DEFAULT 0.00,
                                    topAvgMarketValue DECIMAL(10, 2) DEFAULT 0.00,
                                    downAvgMarketValue DECIMAL(10, 2) DEFAULT 0.00,
                                    topAvgTurnover DECIMAL(10, 2) DEFAULT 0.00,
                                    downAvgTurnover DECIMAL(10, 2) DEFAULT 0.00,
                                    topTire TEXT(60000) NULL
                                     );  '''
        self.connect.create("dealMarketTest",sql)
        # 找出未更新数据中最远一天的日期
        maxDateTemp = self.dealDate.getNewestDate("dealMarketTest")
        maxDate = maxDateTemp if maxDateTemp is not None else "2023-01-10"
        # 查询交易日信息，目前三点钟后统计当天的数据
        date = list(map(lambda x:x[0],self.dealDate.getPartDate(maxDate,
                (datetime.datetime.now() - datetime.timedelta(hours=15)).strftime('%Y-%m-%d'))))
        # 遍历需要更新的日期，查找当天需要的数据
        for everyDate in date:
            everyDateIndex = date.index(everyDate)
            yesDate = date[everyDateIndex - 1] if everyDateIndex != 0 else maxDate
            dailyData = {
            'date':everyDate,
            'topCount':0,
            'downCount':0,
            'meatCount':0,
            'noodleCount':0,
            'topPreminum':0,
            'downDiscount':0,
            'meatPreminum':0,
            'noodleDiscount':0,
            'growthTopCount':0,
            'growthDownCount':0,
            'growthTopPreminum':0,
            'growthDownDiscount':0,
            'topAvgMarketValue':0,
            'downAvgMarketValue':0,
            'topAvgTurnover':0,
            'downAvgTurnover':0,
            'topTire':""
            }
            # 取出当日大涨、大跌、大面、大肉数，及平均其市值、成交额
            # 大涨
            self.getLimitData("top",everyDate.strftime("%Y-%m-%d"),dailyData)
            # 大跌
            self.getLimitData("down",everyDate.strftime("%Y-%m-%d"),dailyData)
            # 大肉
            self.getFluctuData("meat",everyDate.strftime("%Y-%m-%d"),dailyData)
            # 大面
            self.getFluctuData("noodle",everyDate.strftime("%Y-%m-%d"),dailyData)
            # 创业板大涨数
            self.getGrowthLimitData("top",everyDate.strftime("%Y-%m-%d"),dailyData)
            # 创业板大跌数
            self.getGrowthLimitData("down",everyDate.strftime("%Y-%m-%d"),dailyData)
            # 大涨溢价
            self.getPreminum("top",everyDate.strftime("%Y-%m-%d"),yesDate,dailyData)
            # 大跌折价
            self.getPreminum("down",everyDate.strftime("%Y-%m-%d"),yesDate,dailyData)
            # 大肉溢价
            self.getPreminum("meat",everyDate.strftime("%Y-%m-%d"),yesDate,dailyData)
            # 大面折价
            self.getPreminum("noodle",everyDate.strftime("%Y-%m-%d"),yesDate,dailyData)
            # 创业板大涨溢价
            self.getGrowthPreinum("top",everyDate.strftime("%Y-%m-%d"),yesDate,dailyData)
            # 创业板大跌折价
            self.getGrowthPreinum("down",everyDate.strftime("%Y-%m-%d"),yesDate,dailyData)
            # 获取涨停元组
            self.getTopTire(everyDate.strftime("%Y-%m-%d"),dailyData)

            # 存储当天的处理数据
            sql = ''' insert into dealMarketTest values ('{}',{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},"{}")
                    ON DUPLICATE KEY UPDATE topCount={};  '''.format(dailyData['date'],dailyData['topCount'],
                    dailyData['downCount'],dailyData['meatCount'],dailyData['noodleCount'],dailyData['topPreminum'],
                    dailyData['downDiscount'],dailyData['meatPreminum'],dailyData['noodleDiscount'],
                    dailyData['growthTopCount'],dailyData['growthDownCount'],dailyData['growthTopPreminum'],
                    dailyData['growthDownDiscount'],dailyData['topAvgMarketValue'],dailyData['downAvgMarketValue'],
                    dailyData['topAvgTurnover'],dailyData['downAvgTurnover'],dailyData['topTire'],dailyData['topCount'])
            self.connect.insert("dealMarketTest",sql)
            self.connect.commit()
